package com.ccp.dev.core.basic.table.impl;

import com.ccp.dev.core.basic.db.datasource.JdbcTemplateUtil;
import com.ccp.dev.core.basic.page.PageBean;
import com.ccp.dev.core.basic.table.*;
import com.ccp.dev.core.basic.table.colmap.OracleColumnMap;
import com.ccp.dev.core.util.StringUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;


/**
 * postgreSql 视图操作类。
 *
 * @author ray
 */
@Component
public class PostgreSqlDbView extends BaseDbView implements IDbView {
    @Resource
    private JdbcTemplate jdbcTemplate;

    private static final String sqlAllView = "select viewname from pg_views WHERE schemaname ='public'";

    static final String SQL_GET_COLUMNS_BATCH="select" +
            " table_name,column_name,is_nullable,data_type,character_octet_length length," +
            " numeric_precision precisions,numeric_scale scale" +
            " from" +
            " information_schema.columns " +
            " where table_schema='public' ";

    /**
     * 获取系统视图列表。
     */
    @Override
    public List<String> getViews(String viewName) throws SQLException {
        String sql = sqlAllView;
        if (StringUtil.isNotEmpty(viewName)) {
            sql += " where lower(viewname) like '" + viewName.toLowerCase() + "%'";
        }
        return this.jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 转换数据类型。
     */
    @Override
    public String getType(String type) {
        type = type.toLowerCase();
        if (type.indexOf("numeric") > -1) {
            return ColumnModel.COLUMNTYPE_NUMBER;
        } else if (type.indexOf("date") > -1) {
            return ColumnModel.COLUMNTYPE_DATE;
        } else if (type.indexOf("char") > -1) {
            return ColumnModel.COLUMNTYPE_VARCHAR;
        }
        return ColumnModel.COLUMNTYPE_VARCHAR;
    }

    @Override
    public List<String> getViews(String viewName, PageBean pageBean)
            throws Exception {
        String sql = sqlAllView;
        if (StringUtil.isNotEmpty(viewName)) {
            sql += " where viewname like '" + viewName.toLowerCase() + "%'";
        }
        return getForList(sql, pageBean, String.class, SqlTypeConst.POSTGRESQL);
    }

    @Override
    public List<TableModel> getViewsByName(String viewName, PageBean pageBean) throws Exception {
        String sql = sqlAllView;
        if (StringUtil.isNotEmpty(viewName)) {
            sql += " WHERE viewname LIKE '%" + viewName.toUpperCase() + "%'";
        }
        RowMapper<TableModel> rowMapper = new RowMapper<TableModel>() {

            @Override
            public TableModel mapRow(ResultSet rs, int row)
                    throws SQLException {
                TableModel tableModel = new TableModel();
                tableModel.setName(rs.getString("viewname"));
                return tableModel;
            }
        };
        List<TableModel> tableModels = getForList(sql, pageBean, rowMapper, SqlTypeConst.POSTGRESQL);
        //获取列对象
        List<String> tableNames = new ArrayList<>();
        for (TableModel model : tableModels) {
            tableNames.add(model.getName());
        }
        Map<String, List<ColumnModel>> tableColumnsMap = getColumnsByTableName(tableNames);
        for (Entry<String, List<ColumnModel>> entry : tableColumnsMap.entrySet()) {
            for (TableModel model : tableModels) {
                if (model.getName().equalsIgnoreCase(entry.getKey())) {
                    model.setColumnList(entry.getValue());
                }
            }
        }

        return tableModels;
    }

    /**
     * 根据表名获取列。此方法使用批量查询方式。
     *
     * @param tableNames
     * @return
     */
    private Map<String, List<ColumnModel>> getColumnsByTableName(List<String> tableNames) {
        String sql = SQL_GET_COLUMNS_BATCH;
        Map<String, List<ColumnModel>> map = new HashMap<>();
        if (tableNames != null && tableNames.size() == 0) {
            return map;
        } else {
            StringBuffer buf = new StringBuffer();
            for (String str : tableNames) {
                buf.append("'" + str + "',");
            }
            buf.deleteCharAt(buf.length() - 1);
            sql += " AND A.TABLE_NAME IN (" + buf.toString() + ") ";
        }

        List<ColumnModel> columnModels = JdbcTemplateUtil.getNamedParameterJdbcTemplate(jdbcTemplate).query(sql, new HashMap<String, Object>(), new OracleColumnMap());
        for (ColumnModel columnModel : columnModels) {
            String tableName = columnModel.getTableName();
            if (map.containsKey(tableName)) {
                map.get(tableName).add(columnModel);
            } else {
                List<ColumnModel> cols = new ArrayList<ColumnModel>();
                cols.add(columnModel);
                map.put(tableName, cols);
            }
        }
        return map;
    }

    @Override
    public void createOrRep(String viewName, String sql) throws Exception {
        String getSql = "CREATE OR REPLACE VIEW " + viewName + " as (" + sql + ")";
        jdbcTemplate.execute(getSql);
    }

}
